set spark.sql.crossJoin.enabled=true;
with accept as (--受理网点  --1邮政工单量appeal_cnt 2.第三方量third_platform_cnt 3.网点客诉量site_cnt
    select dt
         , accept_network_code                                     as network_code
         , 0                                                       as work_cnt           -- 呼叫中心
         , 0                                                       as project_cnt        -- 平台
         , sum(if(work_type = '邮政工单', 1, 0))                       as appeal_cnt         -- 邮政
         , 0                                                       as claim_cnt
         , sum(if(work_type = '邮政工单' and duty_type = '第三方', 1, 0)) as third_platform_cnt --三方
         , 0                                                       as transfer_cnt_total
         , sum(if(work_type = '邮政工单' and duty_type = '网点', 1, 0))  as site_cnt
         , 0                                                       as taking_cnt
         , 0                                                       as arrival_cnt
    from jms_dm.dm_waybill_customer_complaint_detail_new_hi
    where dt = '{{ execution_date | cst_ds }}'
      and is_delete = 2 --未删除
      and accept_proxy_code <> '888888'
    group by dt, accept_network_code
),
-- 责任网点-代理区
     duty as (--责任网点    1普通工单量work_cnt 2.平台工单project_cnt 3.理赔工单claim_cnt 4.第三方third_platform_cnt 5.转单transfer_cnt_total 6.网点客诉量site_cnt
         select dt
              , duty_network_code                                                           as network_code
              , sum(if(work_type = '普通工单', 1, 0))                                           as work_cnt
              , sum(if(work_type = '平台工单', 1, 0))                                           as project_cnt
              , 0                                                                           as appeal_cnt
              , sum(if(work_type = '理赔工单', 1, 0))                                           as claim_cnt
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '第三方', 1, 0))  as third_platform_cnt
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '转运中心', 1, 0)) as transfer_cnt_total
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '网点', 1, 0))   as site_cnt
              , 0                                                                           as taking_cnt
              , 0                                                                           as arrival_cnt
         from jms_dm.dm_waybill_customer_complaint_detail_new_hi
         where dt = '{{ execution_date | cst_ds }}'
           and is_delete = 2 --未删除
           and duty_proxy_code <> '888888'
         group by dt, duty_network_code
     ),
     proxy_merge as (--代理区分母到件量,揽收量
         -- 代理区网点收件量-同一运单在同一天只记作一次
         select dt,
                network_code,
                0                as work_cnt,
                0                as project_cnt,
                0                as appeal_cnt,
                0                as claim_cnt,
                0                as third_platform_cnt,
                0                as transfer_cnt_total,
                0                as site_cnt,
                sum(taking_cnt)  as taking_cnt,
                sum(arrival_cnt) as arrival_cnt
         from (
                  select pick_network_code as network_code
                       , count(1)          as taking_cnt
                       , 0                 as arrival_cnt
                       , dt
                  from (
                           select waybill_no,
                                  pick_network_code,
                                  dt,
                                  0 as fenmu_tichu_flag

                           from jms_dwd.dwd_yl_oms_oms_waybill_last_hour_status
                           where dt = '{{ execution_date | cst_ds }}'
                       ) tt
                           left join
                       jms_dim.dim_network_whole_massage net
                       on pick_network_code = net.code
                  where net.network_type = 6
                  group by pick_network_code, dt
                  union all
                  select scan_site_code    as netwrok_code
                       , 0                 as taking_cnt
                       , count(waybill_no) as arrival_cnt
                       , dt
                  from (
                           select scan_site_code
                                , waybill_no
                                , to_date(scan_time) as dt
                           from jms_dwd.dwd_tab_barscan_sitearrival_base_hi
                           where dt <= '{{ execution_date | cst_hour }}'
                             and dt >= '{{ execution_date | cst_ds }}-00'
                             and to_date(scan_time) = '{{ execution_date | cst_ds }}'
                             and waybill_no like 'JT%'
                           group by scan_site_code
                                  , waybill_no
                                  , to_date(scan_time)
                       ) arrival
                           left join jms_dim.dim_network_whole_massage net
                                     on scan_site_code = net.code
                  where net.network_type = 6
                  group by arrival.scan_site_code, dt
              ) merges
         group by network_code, dt
     ),
     complaint_qiwei as (
         select network_code
              , dt
              , sum(if((work_type = '普通工单' and second_type_code = '2104') or (work_type = '平台工单'), 1,
                       0))                                                         as xijia_sign_num        --虚假签收
              , sum(if((work_type = '普通工单' and second_type_name = '超时效催件'), 1, 0)) as over_time_num         --超时效催件
              , sum(if((work_type = '普通工单' and second_type_name = '有揽未发'), 1, 0))  as have_take_no_send_num --有揽未发
              , sum(if((work_type = '普通工单' and second_type_name = '不送货上门'), 1, 0)) as no_send_door_num      --不送货上门
              , sum(if((work_type = '普通工单' and second_type_name = '服务态度'), 1, 0))  as service_attitude_num  --服务态度
              , sum(if((work_type = '普通工单' and second_type_name = '费用问题'), 1, 0))  as money_problem_num     --费用问题
              , sum(if((work_type = '普通工单' and second_type_name = '外投意向'), 1, 0))  as out_complaint_num     --外投诉意向
              , sum(if((work_type = '普通工单' and second_type_name = '赔偿意向') or (work_type = '理赔工单'), 1,
                       0))                                                         as compensate_num        --赔偿意向
              , sum(if(reback_transfer_falg = 1, 1, 0))                            as reback_transfer_num   --退转件票数
         from (
                  select waybill_no                                                                     --运单号
                       , work_type                                                                      --工单类型
                       , second_type_code                                                               --二级问题类型编码
                       , second_type_name                                                               --二级问题类型名称
                       , reback_transfer_falg                                                           --退转件中:是否退回,1是0否
                       , if(work_type = '邮政工单', accept_network_code, duty_network_code) as network_code --统计网点编码
                       , dt
                  from jms_dm.dm_waybill_customer_complaint_detail_new_hi
                  where dt = '{{ execution_date | cst_ds }}'
                    and is_delete = 2 --未删除
              ) t
         group by network_code
                , dt
     ),
     merge1 as (
         select dt,
                network_code,
                sum(work_cnt)           as work_cnt,
                sum(project_cnt)        as project_cnt,
                sum(appeal_cnt)         as appeal_cnt,
                sum(third_platform_cnt) as third_platform_cnt,
                sum(transfer_cnt_total) as transfer_cnt_total,
                sum(claim_cnt)          as claim_cnt,
                sum(taking_cnt)         as taking_cnt,
                sum(arrival_cnt)        as arrival_cnt,
                sum(site_cnt)           as site_cnt
         from (
                  select *
                  from accept
                  union
                  select *
                  from duty
                  union
                  select *
                  from proxy_merge
              ) t
         where network_code is not null
         group by dt, network_code --, duty_proxy_code
     ),
     net_whole as (
         select t1.code
              , t1.name
              , t1.fran_code
              , t1.fran_name
              , t1.agent_code
              , t1.agent_name
              , t1.virt_code
              , t1.virt_name
              , t1.zone_id
              , t1.zone_code
              , t1.zone_name
              , t1.sys_area_id
              , t1.parent_area_code
              , t1.parent_area_name
              , t1.regional_id
              , t1.regional_desc
              , t1.network_type
              , t1.is_virtual_agent
              , t1.area_code
              , t1.area_name
              , t2.dt
         from (
                  --逻辑调整,去code,name,做网点类型限定
                  select *
                  from jms_dim.dim_network_whole_massage
                  where name not like '%BEST%'
              ) t1
                  join
              (
                  select date_mid_desc as dt
                  from jms_dim.dim_date_info_base
                  where date_mid_desc = '{{ execution_date | cst_ds }}'
              ) t2
              on 1 = 1
     )
insert
overwrite
table
jms_dm.dm_waybill_customer_complaint_network_agg_new_hi
partition
(
dt
)
select net1.regional_id                                                                        as regional_code
     , net1.regional_desc                                                                      as regional_desc
     , net1.code                                                                               as network_code
     , net1.name                                                                               as network_name
     , net1.fran_code                                                                          as fran_code             --加盟商code
     , net1.fran_name                                                                          as fran_name             --加盟商name
     , net1.agent_code                                                                         as agent_code            --代理区code
     , net1.agent_name                                                                         as agent_name            --代理区name
     , net1.virt_code                                                                          as virt_code             --虚拟代理区code
     , net1.virt_name                                                                          as virt_name             --虚拟代理区name
     , net1.zone_id                                                                            as zone_id               --网管片区负责人id
     , net1.zone_code                                                                          as zone_code             --网管片区负责人code
     , net1.zone_name                                                                          as zone_name             --网管片区负责人name
     , net1.sys_area_id                                                                        as sys_area_id           --网管父片区id
     , net1.parent_area_code                                                                   as parent_area_code      --网管父片区域code
     , net1.parent_area_name                                                                   as parent_area_name      --网管父片区域name
     , net1.area_code                                                                          as area_code
     , net1.area_name                                                                          as area_name
     , net1.network_type                                                                       as network_type          --网点类型
     , net1.is_virtual_agent                                                                   as is_virtual_agent      --1是虚拟代理区,2不是
     , nvl(t1.work_cnt, 0)                                                                     as work_cnt              -- 呼叫中心客诉量
     , nvl(t1.work_total, 0)                                                                   as work_total            -- 呼叫中心客诉量汇总
     , nvl(t1.project_cnt, 0)                                                                  as project_cnt           -- 平台客诉量
     , nvl(t1.project_total, 0)                                                                as project_total         -- 平台客诉量汇总
     , nvl(t1.appeal_cnt, 0)                                                                   as appeal_cnt            -- 邮政客诉量
     , nvl(t1.appeal_total, 0)                                                                 as appeal_total          -- 邮政客诉量汇总
     , nvl(t1.claim_cnt, 0)                                                                    as claim_cnt             -- 理赔客诉量
     , nvl(t1.claim_total, 0)                                                                  as claim_total           -- 理赔客户量汇总
     , nvl(third_platform_cnt, 0)                                                              as third_platform_cnt
     , round(if(taking_cnt > 0 or arrival_cnt > 0
                 , nvl(third_platform_cnt, 0)
                       / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                               as third_platform_ratio
     , nvl(third_platform_total, 0)                                                            as third_platform_total
     , round(if(taking_cnt > 0 or arrival_cnt > 0
                 , nvl(third_platform_total, 0)
                       / (nvl(taking_total, 0) * 0.15 + nvl(arrival_total, 0) * 0.85) * 10000, 0), 2)
                                                                                               as third_platform_total_ratio
     , nvl(transfer_cnt, 0)                                                                    as transfer_cnt
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                nvl(transfer_cnt, 0)
                    / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                               as transfer_cnt_ratio
     , nvl(transfer_total, 0)                                                                  as transfer_total
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                nvl(transfer_total, 0)
                    / (nvl(taking_total, 0) * 0.15 + nvl(arrival_total, 0) * 0.85) * 10000, 0), 2)
                                                                                               as transfer_total_ratio
     , nvl(network_cnt, 0)                                                                     as network_cnt
     , nvl(network_total, 0)                                                                   as network_total
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                nvl(network_cnt, 0)
                    / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                               as network_ratio
     , round(if(taking_cnt > 0 or arrival_cnt > 0
                 , nvl(network_total, 0)
                       / (nvl(taking_total, 0) * 0.15 + nvl(arrival_total, 0) * 0.85) * 10000, 0), 2)
                                                                                               as network_total_ratio
     , nvl(all_cnt, 0)                                                                         as all_cnt
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                nvl(all_cnt, 0) /
                ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                               as all_ratio
     , nvl(work_total, 0) + nvl(project_total, 0) + nvl(appeal_total, 0) + nvl(claim_total, 0) as all_total
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                (nvl(work_total, 0) + nvl(project_total, 0) + nvl(appeal_total, 0) + nvl(claim_total, 0))
                    / (nvl(taking_total, 0) * 0.15 + nvl(arrival_total, 0) * 0.85) * 10000, 0), 2)
                                                                                               as all_total_ratio
     , nvl(t1.taking_cnt, 0)                                                                   as taking_cnt
     , nvl(t1.arrival_cnt, 0)                                                                  as arrival_cnt
     , nvl(t1.taking_total, 0)                                                                 as taking_total
     , nvl(t1.arrival_total, 0)                                                                as arrival_total
     , net1.dt                                                                                 as date_time             --明细的dt,是create_time,统计的是t-1的数据
     , nvl(t3.xijia_sign_num, 0)                                                               as xijia_sign_num        --普通工单虚假签收
     , nvl(t3.over_time_num, 0)                                                                as over_time_num         --普通工单超时效催件
     , nvl(t3.have_take_no_send_num, 0)                                                        as have_take_no_send_num --普通工单有揽未发
     , nvl(t3.no_send_door_num, 0)                                                             as no_send_door_num      --普通工单不送货上门
     , nvl(t3.service_attitude_num, 0)                                                         as service_attitude_num  --普通工单服务态度
     , nvl(t3.money_problem_num, 0)                                                            as money_problem_num     --普通工单费用问题
     , nvl(t3.out_complaint_num, 0)                                                            as out_complaint_num     --普通工单外投诉意向
     , nvl(t3.compensate_num, 0)                                                               as compensate_num        --普通工单赔偿意向
     , nvl(t3.reback_transfer_num, 0)                                                          as reback_transfer_num   --普通工单退转件票数
     , nvl(t4.arr_ctn, 0)                                                                      as center_op             --中心操作量
     , net1.dt
from net_whole net1
         left join
     (
         select network_code                                                                    as network_code
              , nvl(work_cnt, 0)                                                                as work_cnt      -- 呼叫中心客诉量
              , sum(nvl(work_cnt, 0)) over (partition by dt)                                    as work_total    -- 呼叫中心客诉量汇总
              , nvl(project_cnt, 0)                                                             as project_cnt   -- 平台客诉量
              , sum(nvl(project_cnt, 0)) over (partition by dt)                                 as project_total -- 平台客诉量汇总
              , nvl(appeal_cnt, 0)                                                              as appeal_cnt    -- 邮政客诉量
              , sum(nvl(appeal_cnt, 0)) over (partition by dt)                                  as appeal_total  -- 邮政客诉量汇总
              , nvl(claim_cnt, 0)                                                               as claim_cnt     -- 理赔客诉量
              , sum(nvl(claim_cnt, 0)) over (partition by dt)                                   as claim_total   -- 理赔客户量汇总
              , nvl(third_platform_cnt, 0)                                                      as third_platform_cnt
              , sum(nvl(third_platform_cnt, 0)) over (partition by dt)                          as third_platform_total
              , nvl(transfer_cnt_total, 0)                                                      as transfer_cnt
              , sum(nvl(transfer_cnt_total, 0)) over (partition by dt)                          as transfer_total
              , nvl(site_cnt, 0)                                                                as network_cnt
              , sum(nvl(site_cnt, 0)) over (partition by dt)                                    as network_total
              , nvl(work_cnt, 0) + nvl(project_cnt, 0) + nvl(appeal_cnt, 0) + nvl(claim_cnt, 0) as all_cnt
              , nvl(taking_cnt, 0)                                                              as taking_cnt
              , nvl(arrival_cnt, 0)                                                             as arrival_cnt
              , dt                                                                              as date_time     --明细的dt,是create_time,统计的是t-1的数据
              , sum(nvl(taking_cnt, 0)) over (partition by dt)                                  as taking_total
              , sum(nvl(arrival_cnt, 0)) over (partition by dt)                                 as arrival_total
              , dt
         from merge1
     ) t1
     on net1.code = t1.network_code
         and net1.dt = t1.dt
         left join complaint_qiwei t3
                   on net1.code = t3.network_code
                       and net1.dt = t3.dt
         left join
     (
         select scan_site_code  as scan_site_code
              , sum(oper_cnt) as arr_ctn --操作量
              , dt
         from jms_dm.dm_center_transfer_summary_new_dt
         where dt = '{{ execution_date | cst_ds }}'
           and sum_flag_code = 'zrr'
         group by scan_site_code
                , dt
     ) t4
     on net1.code = t4.scan_site_code
         and net1.dt = t4.dt
    distribute by net1.dt
;
